package com.projec.dao.impl;

import com.projec.bean.LinkBean;
import com.projec.dao.ILinkDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 通讯录持久接口实现类
 */
public class LinkDaoImpl implements ILinkDao {

    public static void main(String[] args) {
        ILinkDao dao=new LinkDaoImpl();

       dao.findAll();
//        System.out.println(dao.findId(3));

        System.out.println( dao.findByName("王"));
    }

    @Override
    public void add(LinkBean linkObj) {
        //链接对象
        Connection con =null;
        //SQL语句执行对象
        PreparedStatement ps=null;
        try {
            // 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2.建立链接 localhost 表示链接数据库所在服务器的IP地址 12345为链接的端口
            //hw表示链接mysql 数据库的库名 characterEncoding=utf-8 设置编码集
            //root 表示登录 mysql 的用户名 lovo 表示 mysql 的密码。
            con= DriverManager.getConnection(
                    "jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");

            //3.执行SQL语句
                    ps = con.prepareStatement("insert into t_link(l_name,l_birthday,l_sex,l_phone)"+
                    "values (?,?,?,?)");
            // 填充占位符
            ps.setObject(1,linkObj.getName());
            ps.setObject(2,linkObj.getBirthday());
            ps.setObject(3,linkObj.getSex());
            ps.setObject(4,linkObj.getPhone());
            //更新数据库，将数据真正写入数据库
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                //关闭链接
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public void del(int id) {
        Connection con=null;
        PreparedStatement ps=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("delete from t_link where pk_linkId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public void update(int id, String phone) {
   Connection con=null;
  PreparedStatement ps=null;
        try {

            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");

            ps=con.prepareStatement("update t_link set l_phone=?where pk_linkId=?");
            ps.setObject(1,phone);
            ps.setObject(2,id);
            ps.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public List<LinkBean> findAll() {
        List<LinkBean> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");
           ps=con.prepareStatement("select * from t_link");
//           将查询结果放到结果集对象中

           rs=ps.executeQuery();
           //将结果集指针指向下一条记录 ，如果返回值为false ，表示没有下一条记录，遍历结束。
          while (rs.next()){
           list.add( cv(rs));
          }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }


        return list;
    }

    @Override
    public LinkBean findId(int id) {
    LinkBean linkBean=null;
    Connection con=null;
    PreparedStatement ps=null;
    ResultSet rs=null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select * from t_link where pk_linkId=?");
            ps.setObject(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                linkBean=cv(rs);
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return linkBean;
    }

    @Override
    public List<LinkBean> findByName(String name) {
        List<LinkBean> list= new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/hw?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select * from t_link where l_name like?");
            ps.setObject(1,"%"+name+"%");
            rs=ps.executeQuery();
            while (rs.next()){
               list.add(cv(rs));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }


        return list;
    }

    public LinkBean cv( ResultSet rs) throws SQLException {
        LinkBean linkObj=new LinkBean();
        linkObj.setId(rs.getInt("pk_linkId"));
        linkObj.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
        linkObj.setName(rs.getString("l_name"));
        linkObj.setSex(rs.getString("l_sex"));
        linkObj.setPhone(rs.getString("l_phone"));
        return linkObj;
    }
}
